﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace Cleware.Debit3.Tools.EdmUtil
{
    public class DA 
    {
        public string ConnectionString { get; set; }
        public static bool ConnectionIsValid(string connectionString)
        {
            bool retval;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                }

                retval = true;
            }
            catch
            {
                retval = false;
            }
            return retval;
        }

        public DA(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        public DataSet GetResultSchema(string spName, List<string> parameters)
        {
            SqlConnection connection = null;
            SqlTransaction trn = null;
            DataSet result = new DataSet();

            try
            {
                connection = GetConnection();
                trn = connection.BeginTransaction();

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.Transaction = trn;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = spName;
                command.CommandTimeout = 1000;

                if (parameters != null)
                {
                    foreach (string parameterName in parameters)
                        command.Parameters.Add(new SqlParameter("@"+ parameterName, DBNull.Value));
                }

                SqlDataReader myReader;
                myReader = command.ExecuteReader(CommandBehavior.KeyInfo);
                while (true)
                {
                    //Retrieve column schema into a DataTable.
                    DataTable schemaTable = new DataTable();
                    schemaTable = myReader.GetSchemaTable();
                    if (schemaTable != null)
                        result.Tables.Add(schemaTable);

                    if (!myReader.NextResult())
                        break; ;
                }

                myReader.Close();

                result.WriteXml(string.Format("{0}_schema.xml", spName));
            }
            catch (Exception ex)
            {
                Logger.Instance.Log(Logger.LogType.Error, string.Format ("\tHiba történt a séma lekérdezése közben! Tárolt eljárás: {0}", spName), ex);
                if (trn != null)
                {
                    trn.Rollback();
                    trn = null;
                }
            }
            finally
            {
                if (trn != null)
                {
                    trn.Rollback();
                    trn = null;
                }

                if (connection != null)
                    connection.Close();
            }

            return result;
        }
       
        public string GetAllDescriptionFromDatabase(string sqlQuery)
        {
            SqlConnection connection = null;
            string result = string.Empty;
            string text = sqlQuery;
            try
            {
                connection = this.GetConnection();               
                var command = new SqlCommand
                    {
                        Connection = connection,
                        CommandType = System.Data.CommandType.Text,
                        CommandText = text,
                        CommandTimeout = 1000
                    };

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result += reader[0].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Instance.Log(
                    Logger.LogType.Error, 
                    string.Format("\tHiba történt az item description-ök lekérdezése közben! \r\n{0}", text), 
                    ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }

            return result;
        }


        private SqlConnection GetConnection()
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            connection.Open();
            return connection;
        }
        private int ExecuteNonQuery(SqlConnection conn, SqlTransaction tran, SqlCommand command, out object RETURN_VALUE)
        {
            command.Transaction = tran;
            command.Connection = conn;
            int result = command.ExecuteNonQuery();

            RETURN_VALUE = null;
            if (command.Parameters.Contains("RETURN_VALUE"))
                RETURN_VALUE = command.Parameters["RETURN_VALUE"].Value;
            return result;
        }
        private void AddParam(SqlCommand comm, string name, SqlDbType sqlType, int size, ParameterDirection direction, object value)
        {
            SqlParameter param = new SqlParameter(name, sqlType);
            param.Direction = direction;
            param.Value = value;
            if (sqlType != SqlDbType.Binary && sqlType != SqlDbType.VarBinary && sqlType != SqlDbType.NText && sqlType != SqlDbType.Text && sqlType != SqlDbType.Image)
                param.Size = size;
            comm.Parameters.Add(param);

        }
    }
}
